package business.database.newcorps;

import groovy.lang.Closure;
import groovy.sql.Sql;

import java.util.List;

import org.apache.log4j.Logger;
import business.ViewLogger;
import business.coding.CandidateCenter;
import business.database.filtersemantic.CorpsFilterEnum;
import business.database.filtersemantic.CorpsFilterSQLGen;
import domain.Center

/**
 * Acceso a la tabla de precodecorp
 * @author 53385255R
 *
 */
public class PreCodeCorpDAO {
	
	private Logger _log;
	private def Sql sqlnewcorps;
	public PreCodeCorpDAO(Sql sql) {
		sqlnewcorps = sql;
		_log = Logger.getLogger(PreCodeCorpDAO.class);
	}
	/**
	* Crea la tabla PreCodeCorp para almacenar los centros candidatos
	*/
   public void create() {
	   //se crea la tabla de direcciones codificadas si no existe
	   try {
		   sqlnewcorps.execute("create table PreCodeCorp (idcodecorp integer, corp varchar(255), " +
		   "ISO varchar(5)," +
		   "PROVINCIA varchar(2)," +
		   "INSTIT varchar(4)," +
		   "ORGANISMO varchar(6)," +
		   "DPTO varchar(8)," +
		   "SECTOR varchar(8)," +
		   "context double," +
		   "permatch double," +
		   "autoCod integer)")
	   
	   		sqlnewcorps.execute("create index idx_PreCodeCorp on PreCodeCorp(idcodecorp)");
	   } catch (Exception e) {
		   _log.error ("No se ha podido crear la tabla PreCodeCorp. Probablemente ya exista.");
	   }
   }
   
   /**
    * Maximo idcodecorp en esta tabla
    * @return
    */
   public Integer selectMaxidCodeCorp_FromPreCodeCorp(){
	   
	   //select max(idcodecorp) from (SELECT idcodecorp FROM corp union select idcodecorp from poscodecorps union select idcodecorp from precodecorp)
	   String query = "Select max(idcodecorp) as MAXID from PreCodeCorp"
	   def maxid = sqlnewcorps.firstRow(query)
	   if ((maxid != null) && (maxid[0] != null))
		   return maxid[0]
	   else return 0
   }
   
   /**
   * Obtiene el corp de idcodecorp
   * @param idcodecorp
   * @return
   */
   public String selectCorp(int idcodecorp) {
	   def corp = sqlnewcorps.firstRow ("select corp from PreCodeCorp where idcodecorp = ${idcodecorp}")
	   if (corp != null && corp[0] != null)
		   return corp
	   else return ""
   }
   
   /**
   * Obtiene el identificador del corp si existe. Null si no
   * @param corp
   * @return
   */
  public Integer selectIdCorp(String corp) {
	  Integer idcodecorp = null;
	  def codecorp = sqlnewcorps.firstRow("select idcodecorp from PreCodeCorp where corp = ?", [corp])
	  if ((codecorp != null) && (codecorp[0] != null))
		  idcodecorp = codecorp[0]
		  
	   return idcodecorp;
  }
  
  /**
   * Inserta un nuevo precodecorp 
   */
  public void insert(int idcodecorp, String corp, String ISO, String  PROVINCIA, String  INSTIT, String  ORGANISMO, String  DPTO, String  SECTOR, int ContextRange, double permatch, int autoCod) {
	  sqlnewcorps.execute("INSERT INTO PreCodeCorp VALUES (${idcodecorp},?,?,?,?,?,?,?,${ContextRange},${permatch}, ${autoCod})", [corp, ISO, PROVINCIA,INSTIT,ORGANISMO, DPTO, SECTOR]);
  }
  
  /**
  * Nos dice cuantos corps hay con candidatos
  * @return numero de corps con centros candidatos
  */
 public Integer countDistinct() {
	 def cnt = sqlnewcorps.firstRow("select count(*) from (SELECT corp  from precodecorp group by corp)")
	 if ((cnt != null) && (cnt[0] != null))
		 return cnt[0]
	 else return 0
 }
 
 /**
  * Ejecuta el closure sobre el resutlado de una busqueda para un idcodecorp
  * @param thRg se filtran candidatos de rango mayor o igual a thRg
  * @param idcodecorp identificador del corp
  * @param closure
  */
 public void iteratePreCodeCorp(int thRg, int idcodecorp, int autocod, Closure closure) {
	
	 sqlnewcorps.eachRow("select * from PreCodeCorp where idcodecorp = ${idcodecorp} and context >= ${thRg} and autoCod >= ${autocod}", closure);

 }
 
 /**
 * Itera sobre un rango de idcodecorps ejecutando un closure
 * @param id1
 * @param id2
 * @param ahead indica si hay que ir para adelante a para atras
 * @param closure
 */
public void iterateRange(int id1, int id2, boolean ahead, Closure closure) {
		
			String sqlpre = "select distinct idcodecorp, corp from Precodecorp where idcodecorp >= ${id1} and idcodecorp <= ${id2}"
			
			if (!ahead) {
			
				sqlpre = "select distinct idcodecorp, corp from Precodecorp where idcodecorp >= ${id2} and idcodecorp <= ${id1}"
			}
				
	sqlnewcorps.eachRow(sqlpre, closure);
}

public void iterateWithCorp(int idcodecorp, boolean ahead, CorpsFilterEnum  filterEnum, Closure closure) {
	
	String order = " order by launion.idcodecorp asc";
	if (!ahead) order = " order by launion.idcodecorp desc";
	
	CorpsFilterSQLGen sqlfilter = new CorpsFilterSQLGen("","launion","");
	String filter = sqlfilter.getFilter(filterEnum);
	//if (filter != "") filter = filter.replaceAll(" and "," where ");
	if (filterEnum == CorpsFilterEnum.All) filter = "";
	filter = filter + order;
	//A veces no hay where. Lo limpiamos
	filter = filter.replaceAll("where  order","order");
	filter = filter.replaceAll("and  order","order");
	
		String sqlpre = "select distinct launion.idcodecorp, launion.corp from (select idcodecorp,corp from Precodecorp where idcodecorp > ${idcodecorp} union all select idcodecorp, corp from corp where idcodecorp > ${idcodecorp}) as launion ${filter}"
		
		if (!ahead) {
		
			sqlpre = "select distinct launion.idcodecorp, launion.corp from (select idcodecorp,corp from Precodecorp where idcodecorp < ${idcodecorp} union all select idcodecorp, corp from corp where idcodecorp < ${idcodecorp}) as launion ${filter}"
		}
		
		
			println sqlpre
			sqlnewcorps.eachRow(sqlpre, closure);
}

/**
 * Filtra una consulta y ejecuta el closure sobre idcodecorp y corp resultado
 * @param filter
 * @param closure
 */
public void filter(String corpFilter, String provcombo, String provfilter, String institcombo, String institfilter, String orgcombo, String orgfilter, String dptocombo, String dptofilter,String sectorcombo, String sectorfilter,CorpsFilterEnum  filterEnum, Closure closure) {
	//String[] col = ["corp"];
	CorpsFilterSQLGen sqlfilter = new CorpsFilterSQLGen(corpFilter, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter, sectorcombo,  sectorfilter,"PreCodeCorp","corp"); 

	//Vamos guardandolos todos en la cache
	String where = sqlfilter.getWhere()+ " " + sqlfilter.getAutoCodWhere();
	
	String noSector = sqlfilter.getFilter(filterEnum);

	if (!where.isEmpty()) {
		String sql = "select distinct PreCodeCorp.idcodecorp, PreCodeCorp.corp from PreCodeCorp ${noSector}${where}"
		println sql
		sqlnewcorps.eachRow(sql,closure);
	}
}



public void filterCollaborators(String corpFilter, String provcombo, String provfilter, String institcombo, String institfilter, String orgcombo, String orgfilter, String dptocombo, String dptofilter,String sectorcombo, String sectorfilter, Closure closure) {

	CorpsFilterSQLGen sqlfilter = new CorpsFilterSQLGen(corpFilter, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter, sectorcombo,  sectorfilter,"PosCodeCorps","corp");
	
		//Vamos guardandolos todos en la cache
		String where = sqlfilter.getWhere();
		
		sqlfilter = new CorpsFilterSQLGen(corpFilter, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter, sectorcombo,  sectorfilter,"PosCodeCorpSector","corp");
		
			//Vamos guardandolos todos en la cache
			String where2 = sqlfilter.getWhere();
		
			sqlfilter = new CorpsFilterSQLGen(corpFilter, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter, sectorcombo,  sectorfilter,"VagueCorp","corp");
			
				//Vamos guardandolos todos en la cache
				String where3 = sqlfilter.getWhere();

if (!where.isEmpty()) {
		String sql = "SELECT distinct PreCodeCorp.idcodecorp, PreCodeCorp.corp " +
			"FROM (PreCodeCorp INNER JOIN CORPIDNO ON PreCodeCorp.idcodecorp = CORPIDNO.IDCODECORP) INNER JOIN (SELECT CORPIDNO.IDNO " +
			"FROM CORPIDNO INNER JOIN PosCodeCorps ON CORPIDNO.IDCODECORP = PosCodeCorps.idcodecorp " +
			"WHERE (${where}) " +
			"GROUP BY CORPIDNO.IDNO) as Consulta1 ON CORPIDNO.IDNO = Consulta1.IDNO;"
		
		println sql
		sqlnewcorps.eachRow(sql,closure);
		
		 sql = "SELECT distinct PreCodeCorp.idcodecorp, PreCodeCorp.corp " +
			"FROM (PreCodeCorp INNER JOIN CORPIDNO ON PreCodeCorp.idcodecorp = CORPIDNO.IDCODECORP) INNER JOIN (SELECT CORPIDNO.IDNO " +
			"FROM CORPIDNO INNER JOIN PosCodeCorpSector ON CORPIDNO.IDCODECORP = PosCodeCorpSector.idcodecorp " +
			"WHERE (${where2}) " +
			"GROUP BY CORPIDNO.IDNO) as Consulta1 ON CORPIDNO.IDNO = Consulta1.IDNO;"
	
			println sql
			sqlnewcorps.eachRow(sql,closure);
			
			sql = "SELECT distinct PreCodeCorp.idcodecorp, PreCodeCorp.corp " +
			"FROM (PreCodeCorp INNER JOIN CORPIDNO ON PreCodeCorp.idcodecorp = CORPIDNO.IDCODECORP) INNER JOIN (SELECT CORPIDNO.IDNO " +
			"FROM CORPIDNO INNER JOIN VagueCorp ON CORPIDNO.IDCODECORP = VagueCorp.idcodecorp " +
			"WHERE (${where3}) " +
			"GROUP BY CORPIDNO.IDNO) as Consulta1 ON CORPIDNO.IDNO = Consulta1.IDNO;"
	
			println sql
			sqlnewcorps.eachRow(sql,closure);
	}
}

private String getLogicOp(String combo) {
	if (combo.isEmpty()) return null;
	if (combo.equals("NOT")) return "AND NOT"
	else return combo;
}

/**
 * filtra los corps en base a los comentarios insertados
 * @param corpFilter
 * @param provcombo
 * @param provfilter
 * @param institcombo
 * @param institfilter
 * @param orgcombo
 * @param orgfilter
 * @param dptocombo
 * @param dptofilter
 * @param sectorcombo
 * @param sectorfilter
 * @param filterEnum
 * @param closure
 */
public void filterByComments(String corpFilter, String provcombo, String provfilter, String institcombo, String institfilter, String orgcombo, String orgfilter, String dptocombo, String dptofilter,String sectorcombo, String sectorfilter,CorpsFilterEnum  filterEnum, Closure closure) {
	
	CorpsFilterSQLGen sqlfilter = new CorpsFilterSQLGen(corpFilter, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter, sectorcombo,  sectorfilter,"PreCodeCorp","corp");

	//Vamos guardandolos todos en la cache
	String where = sqlfilter.getWhereForComments()+ " " + sqlfilter.getAutoCodWhere();
	
	String noSector = sqlfilter.getFilter(CorpsFilterEnum.All);

	if (!where.isEmpty()) {
		String sql = "select distinct PreCodeCorp.idcodecorp, PreCodeCorp.corp from Comments inner join PreCodeCorp on comments.idcodecorp = precodecorp.idcodecorp ${noSector}${where}"
		println sql
		sqlnewcorps.eachRow(sql,closure);
	}
}

/**
 * Cuenta el numero de resultados que daria el filtro.
 * Null si el filtro queda vacio
 * @param filter
 * @return
 */
public Integer countfilter(String corpFilter, String provcombo, String provfilter, String institcombo, String institfilter, String orgcombo, String orgfilter, String dptocombo, String dptofilter, String sectorcombo, String sectorfilter,CorpsFilterEnum  filterEnum) {
	
		Integer cnt = null;
	//	String[] col = ["corp"];
		CorpsFilterSQLGen sqlfilter = new CorpsFilterSQLGen(corpFilter, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter,sectorcombo,  sectorfilter,"PreCodeCorp","corp");
		
		//Vamos guardandolos todos en la cache
		String where = sqlfilter.getWhere() + " " + sqlfilter.getAutoCodWhere();
		
		String noSector = sqlfilter.getFilter(filterEnum);

		if (!where.isEmpty()) {
			String sql = "select distinct PreCodeCorp.idcodecorp from PreCodeCorp ${noSector}${where}"
			println sql
			cnt = sqlnewcorps.rows(sql).size();
		}
		
		return cnt;
}


   /**
	* Obtiene todos los centros candidatos para el idcodecorp indicado
	* @param idcodecorp
	* @return listado de centros candidatos
	*/
   public List<CandidateCenter> getPreCodeCorpsCenters(int idcodecorp) {
	   
	   List<CandidateCenter> censW = new ArrayList<CandidateCenter>();
	   
	   sqlnewcorps.eachRow ("select * from PreCodeCorp where idcodecorp = ${idcodecorp}" , {
		   int id = it[0]
		   String corp = it[1]
		   String iso = it[2]
		   String prov = it[3]
		   String instit = it[4]
		   String org = it[5]
		   String dpto = it[6]
		   String sector = it[7]
		   Center cen = new Center(iso, prov, instit, org, dpto, sector);
		   cen.setIdCe idcodecorp

		   Integer range = it[8]
		   Double permatch = it[9]
		   CandidateCenter cenW = new CandidateCenter();
		   cenW.setCenter cen
		   cenW.set_context_by_range(range== null?0:range);
		   cenW.set_permatch permatch
		   
		  
		   
		   censW.add(cenW)
	   })
	   
	   return censW;
   }
   
   
   
   /**
   * Elimina de precodecorp los registros que contienen los centros candidatos para el corp indicado por idcodecorp
   * @param idcodecorp idcodecorp a eliminar todos sus registros
   */
   public void deletePreCodeCorps(int idcodecorp) {
	   
		   sqlnewcorps.execute("delete * from precodecorp where idcodecorp = ${idcodecorp}");
	   
   }
   /**
    * elimina un listado de idcodecorps
    * @param idcodecorps
    */
   public void deletePreCodeCorps(List<Integer> idcodecorps) {
	   
	   String orid = "(";
	   for (Integer id: idcodecorps) 
	   		orid += "${id},"
		orid += ")";
		orid = orid.replaceAll(",\\)",")");
		
		String sql = "delete * from precodecorp where idcodecorp in ${orid}"
	   
		sqlnewcorps.execute(sql);
	   
   }
   /**
    * Devuelve el numero de idcodecorps unicos no existentes en poscodecorp
    * @return
    */
   public int countNoPosCoded() {
	   return sqlnewcorps.rows("select distinct pre.idcodecorp from precodecorp pre left join poscodecorps pos on pos.idcodecorp = pre.idcodecorp where pos.idcodecorp is null").size();
   }
   
   /**
    * Elimina de precodecorp todos los corps ya codificados
    */
public void deleteAllCodedCorps() {
	sqlnewcorps.execute("delete * from precodecorp where idcodecorp in (select idcodecorp from poscodecorps)");
	
}
   
}
